package com.lanvideo.dao;

import com.lanvideo.entity.LearningRecord;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * 学习记录数据访问层
 */
@Repository
public class LearningRecordDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 创建学习记录
     */
    public int createLearningRecord(Integer userId, Integer videoId) {
        String sql = "INSERT INTO learning_record (user_id, video_id, start_time) VALUES (?, ?, CURRENT_TIMESTAMP) " +
                "ON DUPLICATE KEY UPDATE start_time = CURRENT_TIMESTAMP";
        return jdbcTemplate.update(sql, userId, videoId);
    }

    /**
     * 更新学习进度
     */
    public int updateLearningProgress(Integer userId, Integer videoId, Integer duration, 
                                    Integer lastPosition) {
        String sql = "UPDATE learning_record SET total_duration = total_duration + ?, " +
                "last_position = ? " +
                "WHERE user_id = ? AND video_id = ?";
        return jdbcTemplate.update(sql, duration, lastPosition, userId, videoId);
    }

    /**
     * 完成学习
     */
    public int completeLearning(Integer userId, Integer videoId) {
        String sql = "UPDATE learning_record SET completed = TRUE, completion_time = CURRENT_TIMESTAMP " +
                "WHERE user_id = ? AND video_id = ?";
        return jdbcTemplate.update(sql, userId, videoId);
    }

    /**
     * 获取用户的学习记录
     */
    public List<LearningRecord> getUserLearningRecords(Integer userId) {
        String sql = "SELECT lr.*, v.title as video_title, u.name as user_name " +
                "FROM learning_record lr " +
                "LEFT JOIN video v ON lr.video_id = v.id " +
                "LEFT JOIN user u ON lr.user_id = u.id " +
                "WHERE lr.user_id = ? " +
                "ORDER BY lr.start_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningRecord.class), userId);
    }

    /**
     * 获取视频的学习记录
     */
    public List<LearningRecord> getVideoLearningRecords(Integer videoId) {
        String sql = "SELECT lr.*, v.title as video_title, u.name as user_name " +
                "FROM learning_record lr " +
                "LEFT JOIN video v ON lr.video_id = v.id " +
                "LEFT JOIN user u ON lr.user_id = u.id " +
                "WHERE lr.video_id = ? " +
                "ORDER BY lr.start_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningRecord.class), videoId);
    }

    /**
     * 获取用户对特定视频的学习记录
     */
    public LearningRecord getUserVideoLearningRecord(Integer userId, Integer videoId) {
        String sql = "SELECT lr.*, v.title as video_title, u.name as user_name " +
                "FROM learning_record lr " +
                "LEFT JOIN video v ON lr.video_id = v.id " +
                "LEFT JOIN user u ON lr.user_id = u.id " +
                "WHERE lr.user_id = ? AND lr.video_id = ?";
        List<LearningRecord> records = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningRecord.class), userId, videoId);
        return records.isEmpty() ? null : records.get(0);
    }

    /**
     * 获取用户的学习统计
     */
    public LearningRecord getUserLearningStats(Integer userId) {
        String sql = "SELECT " +
                "COUNT(*) as total_videos, " +
                "SUM(total_duration) as total_duration, " +
                "COUNT(CASE WHEN completed = TRUE THEN 1 END) as completed_videos " +
                "FROM learning_record WHERE user_id = ?";
        
        List<LearningRecord> stats = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningRecord.class), userId);
        return stats.isEmpty() ? null : stats.get(0);
    }

    /**
     * 删除学习记录
     */
    public int deleteLearningRecord(Integer id) {
        String sql = "DELETE FROM learning_record WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 获取最近学习记录
     */
    public List<LearningRecord> getRecentLearningRecords(Integer userId, int limit) {
        String sql = "SELECT lr.*, v.title as video_title, u.name as user_name " +
                "FROM learning_record lr " +
                "LEFT JOIN video v ON lr.video_id = v.id " +
                "LEFT JOIN user u ON lr.user_id = u.id " +
                "WHERE lr.user_id = ? " +
                "ORDER BY lr.start_time DESC " +
                "LIMIT ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningRecord.class), userId, limit);
    }
}
